package com.akilesa.dictcreator;

import java.io.*;
import java.sql.*;
import java.util.Calendar;
import java.util.List;

import static java.lang.Class.forName;

/**
 * Created by IntelliJ IDEA.
 * User: akilesa
 * Date: 20.01.2010
 * Time: 12:28:21
 * To change this template use File | Settings | File Templates.
 */
public class AnkiDbManagerImpl {

    static {
        try {
            forName("org.sqlite.JDBC");
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
    }

    public void createAnkiDeck(String filename) throws IOException {
        InputStream in = null;
        OutputStream out = null;
        try {
            in = AnkiDbManagerImpl.class.getClassLoader().getResourceAsStream("empty.anki");
            File file = new File(filename);
            file.createNewFile();
            out = new FileOutputStream(file);
            byte[] buf = new byte[in.available()];
            in.read(buf);
            out.write(buf);
        } finally {
            in.close();
            out.close();
        }
    }

    public void createCards(List<AnkiCard> cards, String dbName) throws SQLException {
        Connection connection = null;
        PreparedStatement statement1 = null;
        PreparedStatement statement2 = null;
        ResultSet rs = null;
        ResultSet generatedKeys = null;
        try {
            connection = DriverManager.getConnection("jdbc:sqlite:" + dbName);
            statement1 =
                    connection.prepareStatement
                            ("insert into cards (ordinal, question, answer, factId,cardModelId," +
                                    "created, modified,tags,priority,interval,lastInterval,due,lastDue," +
                                    "factor,lastFactor,firstAnswered,reps,successive,averageTime," +
                                    "reviewTime,youngEase0,youngEase1,youngEase2,youngEase3,youngEase4," +
                                    "matureEase0,matureEase1,matureEase2,matureEase3,matureEase4,yesCount," +
                                    "noCount,spaceUntil,relativeDelay,isDue,type,combinedDue) " +
                                    "values(?,?,?,?,?,?,?,?,?,?,?,1264159133.136485,0,2.5," +
                                    "2.5,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,2,1264159133.136485)");
            statement1.setQueryTimeout(30);

            statement2 =
                    connection.prepareStatement
                            ("insert into facts (modelId, created, modified, tags,spaceUntil) values(?,?,?,?,?)",
                                    Statement.RETURN_GENERATED_KEYS);
            statement2.setQueryTimeout(30);

            rs = connection.createStatement().executeQuery("select id from models");
            rs.next();
            long modelId = rs.getLong(1);

            rs = connection.createStatement().executeQuery("select id from cardModels where name like 'Forward'");
            rs.next();
            long cardModelId = rs.getLong(1);

            for (int i = 0; i < cards.size(); i++) {
                statement2.setLong(1, modelId);
                statement2.setFloat(2, Calendar.getInstance().getTime().getTime());
                statement2.setFloat(3, Calendar.getInstance().getTime().getTime());
                statement2.setString(4, " ");
                statement2.setFloat(5, 0);
                if (statement2.executeUpdate() == 0) {
                    throw new RuntimeException("Can not insert values");
                }
                generatedKeys = statement2.getGeneratedKeys();
                long factId = 0;
                if (generatedKeys.next()) {
                    factId = generatedKeys.getLong(1);
                } else {
                    throw new RuntimeException("Can not insert values");
                }
                statement1.setInt(1, i);
                statement1.setString(2, cards.get(i).getQuestion());
                statement1.setString(3, cards.get(i).getAnswer());
                statement1.setLong(4, factId);
                statement1.setLong(5, cardModelId);
                statement1.setFloat(6, Calendar.getInstance().getTime().getTime());
                statement1.setFloat(7, Calendar.getInstance().getTime().getTime());
                statement1.setString(8, " ");
                statement1.setLong(9, 2);
                statement1.setFloat(10, 0);
                statement1.setFloat(11, 0);
                statement1.executeUpdate();
            }
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (generatedKeys != null) {
                    generatedKeys.close();
                }
                if (statement1 != null) {
                    statement1.close();
                }
                if (statement2 != null) {
                    statement2.close();
                }
                if (connection != null) {
                    connection.close();
                }
            }
            catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }

    }
}
